﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace IDAdmin.Lib.DataLayer
{
    public class DbHelper
    {
        #region Create Command

        /// <summary>
        /// Tạo SqlCommand với kiểu lệnh là StoredProcedure
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public static SqlCommand CreateCommand(string procName)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        /// <summary>
        /// Tạo SqlCommand với kiểu lệnh là StoredProcedure và kết nối sử dụng là connection
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static SqlCommand CreateCommand(string procName, SqlConnection connection)
        {
            SqlCommand cmd = CreateCommand(procName);
            cmd.Connection = connection;
            return cmd;
        }

        /// <summary>
        /// Tạo SqlCommand
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static SqlCommand CreateCommand(string commandText, CommandType commandType)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            return cmd;
        }

        /// <summary>
        /// Tạo SqlCommand với tham số
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="paramters"></param>
        /// <returns></returns>
        public static SqlCommand CreateCommand(string commandText, CommandType commandType, SqlParameter[] paramters)
        {
            SqlCommand cmd = CreateCommand(commandText, commandType);
            foreach (SqlParameter p in paramters)
            {
                cmd.Parameters.Add(p);
            }
            return cmd;
        }

        public static SqlCommand CreateCommand(string commandText, CommandType commandType, SqlConnection connection)
        {
            SqlCommand cmd = CreateCommand(commandText, commandType);
            cmd.Connection = connection;
            return cmd;
        }

        public static SqlCommand CreateCommand(string commandText, CommandType commandType, SqlParameter[] paramters,SqlConnection connection)
        {
            SqlCommand cmd = CreateCommand(commandText, commandType);
            cmd.Connection = connection;
            foreach (SqlParameter p in paramters)
            {
                cmd.Parameters.Add(p);
            }
            return cmd;
        }

        #endregion

        #region Create Paramter

        public static SqlParameter CreateParamter(string paramterName, SqlDbType dbType)
        {
            SqlParameter p = new SqlParameter(paramterName, dbType);
            return p;
        }

        public static SqlParameter CreateParamter(string paramterName, SqlDbType dbType, object value)
        {
            SqlParameter p = new SqlParameter(paramterName, dbType);
            if (value == null)
                value = DBNull.Value;
            p.Value = value;
            return p;
        }

        public static SqlParameter CreateParamter(string paramterName, SqlDbType dbType, ParameterDirection direction)
        {
            SqlParameter p = new SqlParameter(paramterName, dbType);
            p.Direction = direction;
            return p;
        }

        public static SqlParameter CreateParamter(string paramterName, SqlDbType dbType, ParameterDirection direction, object value)
        {
            SqlParameter p = new SqlParameter(paramterName, dbType);
            p.Direction = direction;
            if (value == null)
                value = DBNull.Value;
            return p;
        }

        #endregion

        #region Execute Reader

        public static SqlDataReader ExecuteReader(SqlCommand command)
        {            
            try
            {
                SqlConnection cn = ConnectionHelper.GetConnection();
                command.Connection = cn;
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;     
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
      
        #endregion

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(SqlCommand command)
        {
            try
            {
                using (SqlConnection cn = ConnectionHelper.GetConnection())
                {
                    command.Connection = cn;
                    int rowsAffected = command.ExecuteNonQuery();
                    cn.Close();
                    return rowsAffected;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// Thực thi câu lệnh (dạng SELECT) và trả về dòng/cột đầu tiên trong kết quả truy vấn
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlCommand selectCommand)
        {
            try
            {
                using (SqlConnection cn = ConnectionHelper.GetConnection())
                {
                    selectCommand.Connection = cn;
                    object returnValue = selectCommand.ExecuteScalar();
                    cn.Close();
                    return returnValue;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region FillDataTable

        /// <summary>
        /// Thực thi câu lệnh (dạng SELECT) và trả về DataTable chứa dữ liệu truy vấn được
        /// </summary>
        /// <param name="selectCommand"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataTable FillDataTable(SqlCommand selectCommand,string tableName)
        {
            try
            {
                using (SqlConnection cn = ConnectionHelper.GetConnection())
                {
                    selectCommand.Connection = cn;
                    DataTable dt = new DataTable(tableName);
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = selectCommand;
                    da.Fill(dt);
                    cn.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region FillDataRow

        /// <summary>
        /// Thực thi câu lệnh (dạng SELECT) và trả về dòng đầu tiên trong dữ liệu truy vấn được
        /// </summary>
        /// <param name="selectCommand"></param>
        /// <returns></returns>
        public static DataRow FillDataRow(SqlCommand selectCommand)
        {
            try
            {
                using (SqlConnection cn = ConnectionHelper.GetConnection())
                {
                    selectCommand.Connection = cn;
                    DataTable dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                    da.Fill(dt);
                    if (dt == null)
                    {
                        return null;
                    }
                    else if (dt.Rows.Count == 0)
                    {
                        return null;
                    }
                    else
                    {
                        return dt.Rows[0];
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
